L'enfer, C'est les Autres... Et Postgres!

Paris Postgres User Group
2024-09-19

Qui suis-je?

• Lætitia Avrot
• Trésorière de PostgreSQL Europe
• Fondatrice de #PostgresWomen
• Practice Leader, Postgres & Security pour EDB
• @l_avrot
• mydbanotebook.org / psql-tips.org

Au programme

• Ce qui est évident
• ce que vous ne comprenez pas
• Ce qui peut arriver
• Ce qui est est complexe
• Ce qui devrait être plus simple

Ce qui est évident

Difficile à écrire

• Postgrè
• Progrès (autocorrect?)
• Progress (autocorrect?)
• Postgre
• Postgres-QL
• Postgras
• Postgre-SQL
• PostgreSequel
• Postgreysql

Impossible à dire

(Voir la FAQ de Postgres wiki)

Solution: 3 noms!!!

• PostgreSQL
• Postgres
• PG

Le Logo

Le Logo

“Peux-tu me donner ton avis sur le logo de Postgres?”

“Je trouve la couleur un peu fade mais sinon ça va.”

“Tu ne trouves pas qu'il fait vieillot?”

“Un peu, mais vous êtes pas une communauté de jeunes non plus.”

Ce que vous ne comprenez pas

Les messages d'erreur de Postgres sont plutôt bon globalement.

• Ça reste un algorithme stupide
• C'est très frustrant pour les humains
• N'essayez pas de relancer sans rien changer!

$ pg_dump --format=directory --file=test -verbose --jobs 5
  --dbname=mydb

pg_dump: last built-in OID is 16383 pg_dump: error:
   no matching extensions were found

$ export PGPASSWORD="******"

$ sudo -u postgres psql -d myDb -w --no-password -t
   -c "SELECT id FROM radusers WHERE id=1"

psql: fe_sendauth: no password supplied

create or replace function allRelevantTeas()
  returns table(tea_id integer) as

$allRelevantTeas$
  declare
    result refcursor;
    stmt text;
    countries_with_tea text[] := array[
      'england',
      'turkey',
      'india',
      'japan',
      'china'];

  begin
    stmt := '';

    for tea_drinker in countries_with_tea loop
      stmt := stmt ||
        format($$(select tea_id from %I)$$, tea_drinker);
      if tea_drinker <> 'china' then
        stmt := stmt || $$ union $$;
      end if;
    end loop;

    open result for execute stmt;
    return result;

  end
$allRelevantTeas$
language plpgsql stable;

select * from allRelevantTeas();

syntax error at or near "countries_with_tea"

create or replace function allRelevantTeas()
  returns table(tea_id integer) as

$allRelevantTeas$
  declare
    result refcursor;
    stmt text;
    countries_with_tea text[] := array[
      'england',
      'turkey',
      'india',
      'japan',
      'china'];

  begin
    stmt := '';

    for tea_drinker in countries_with_tea loop
      stmt := stmt ||
        format($$(select tea_id from %I)$$, tea_drinker);
      if tea_drinker <> 'china' then
        stmt := stmt || $$ union $$;
      end if;
    end loop;

    open result for execute stmt;
    return result;

  end
$allRelevantTeas$
language plpgsql stable;

 for tea_drinker in countries_with_tea loop 

 foreach tea_drinker in countries_with_tea loop 

/* Use psql, the best Postgres client in the world */
\set ON_ERROR_STOP on

/* Making the script idempotent */
drop schema if exists aoc25 cascade;
create schema aoc25;
set search_path to 'aoc25';

/* Insert data into  table */
create table input (
  id integer generated always as identity primary key,
  data text not null
);

\copy input(data) from 'input.csv';

create function snafuToDec(snafu text) returns bigint as
$snafuToDec$
  with snafu(num, l) as (
    select string_to_array(snafu, null),
      char_length(snafu)
  )
  select sum(
      (case when element = '-' then '-1'
      when element = '=' then '-2'
      else element
      end)::bigint
      *
      power(5, l-idx)
    )
  from snafu, unnest(num) with ordinality as a(element, idx);
$snafuToDec$ immutable language sql;

alter table input add column decNumber bigint generated always as (
  snafuToDec(data)) stored;
create table power5 (num bigint, pow integer);
insert into power5 (num,pow) (
  select power(5,n), n
  from generate_series(1,26) t(n)
);

create function pow5(myNumber bigint) returns text as
$pow5$
  with recursive processignpow5(x, num, pow, div, remain, snafu) as (
    (
      /* Get the highest power of 5 */
      select myNumber as x,
        num,
        pow,
        myNumber/num as div,
        myNumber%num as remain,
        (myNumber/num)::text as snafu
      from power5
      where myNumber/num > 0
      order by num desc
      limit 1)
    union all
    (
      select x,
        power5.num,
        power5.pow,
        processignpow5.remain/power5.num as div,
        processignpow5.remain%power5.num as remain,
        processignpow5.snafu || (processignpow5.remain/power5.num)::text
      from processignpow5
        inner join power5
          on processignpow5.pow-1 = power5.pow)
  )
  /* To make the snafu conversion easier, let's add a 0 in the front */
  select '0' || snafu || remain::text
  from processignpow5
  order by num
  limit 1
  ;
$pow5$ language sql;

create function snafu(mydec5 text) returns text as 
$snafu$
  with recursive snafu(n, pow5, snafu) as (
    select 1 as n,
      /* If we have a number over 2 as a last digit, then we need to increase
       * the "new" last digit.
       * By increasing that number we might reach 5, which is bad, obvisouly as
       * this is a number on base 5.
       * So, we then need to increate the next digit and put 0 instead as a last
       * digit.
       * That way, we should never get 5 in the snafu digit */
      case when pow5[array_length(pow5,1)]::int > 2 and array_length(pow5,1) > 3
        /* Now we can safely take into account what happens should we have
         * a 5 digit as a last number in our array */
        then
          case
            when pow5[array_length(pow5,1)-1] = '4'
              then pow5[1:array_length(pow5,1)-3] || (pow5[array_length(pow5,1)-2]::int+1)::text || array['0']
            else pow5[1:array_length(pow5,1)-2] || (pow5[array_length(pow5,1)-1]::int+1)::text
          end
      /* We know we will never get 5 as the digit last digit shouldthe array
       * length be 1 because we on purpose added a 0 in front of the base
       * 5 number we had converted. */  
      when pow5[array_length(pow5,1)]::int > 2 and array_length(pow5,1) = 2
        then array[pow5[1]] || (pow5[2]::int+1)::text
      when pow5[array_length(pow5,1)]::int > 2 and array_length(pow5,1) = 3
        then
          case
            when pow5[array_length(pow5,1)-1] = '4'
              then array[pow5[1]] || (pow5[2]::int+1)::text || array['0']
            else array[pow5[1]] || (pow5[2]::int+1)::text || pow5[3]
          end
      else pow5[1:array_length(pow5,1)-1]
      end as pow5,
      case when pow5[array_length(pow5,1)]::int < 3 then pow5[array_length(pow5,1)]
        when pow5[array_length(pow5,1)]::int = 3 then '='
        when pow5[array_length(pow5,1)]::int = 4 then '-'
      end as snafu
    from (select string_to_array('013140400422344032342', null)) as finalpow5(pow5)
    union all
    select n+1,
      case when pow5[array_length(pow5,1)]::int > 2
        then pow5[1:array_length(pow5,1)-2] || (pow5[array_length(pow5,1)-1]::int+1)::text
      else pow5[1:array_length(pow5,1)-1]
      end as pow5,
      (case when pow5[array_length(pow5,1)]::int < 3 then pow5[array_length(pow5,1)]
        when pow5[array_length(pow5,1)]::int = 3 then '='
        when pow5[array_length(pow5,1)]::int = 4 then '-'
        /* Due to the treatment from before round, we can end up with 5, so we
         * need to take that into account */
        when pow5[array_length(pow5,1)]::int = 5 then '0'
      end) || snafu.snafu as snafu
    from snafu
    where array_length(pow5,1) > 0
  )
  select
    case when substring(snafu from 1 for 1) = '0'
      then substring(snafu from 2)
    else snafu
    end as firstStar
  from snafu
  where snafu is not null
  order by n desc
  limit 1
  ;
$snafu$ language sql;

select snafu(pow5(sum(decNumber)::bigint)) as firstStar
from input;

laetitia=# \i solution.sql

psql:solution.sql:142:
ERROR: column "por5" does not exist

LINE 43:
then por5[1:array_length(pow5,1)-2] || (pow5[array_l...
     ^
HINT: Perhaps you meant to reference the column
"snafu.pow5" or the column "*SELECT* 1.pow5"

/* Use psql, the best Postgres client in the world */
\set ON_ERROR_STOP on

/* Making the script idempotent */
drop schema if exists aoc25 cascade;
create schema aoc25;
set search_path to 'aoc25';

/* Insert data into  table */
create table input (
  id integer generated always as identity primary key,
  data text not null
);

\copy input(data) from 'input.csv';

create function snafuToDec(snafu text) returns bigint as
$snafuToDec$
  with snafu(num, l) as (
    select string_to_array(snafu, null),
      char_length(snafu)
  )
  select sum(
      (case when element = '-' then '-1'
      when element = '=' then '-2'
      else element
      end)::bigint
      *
      power(5, l-idx)
    )
  from snafu, unnest(num) with ordinality as a(element, idx);
$snafuToDec$ immutable language sql;

alter table input add column decNumber bigint generated always as (
  snafuToDec(data)) stored;
create table power5 (num bigint, pow integer);
insert into power5 (num,pow) (
  select power(5,n), n
  from generate_series(1,26) t(n)
);

create function pow5(myNumber bigint) returns text as
$pow5$
  with recursive processignpow5(x, num, pow, div, remain, snafu) as (
    (
      /* Get the highest power of 5 */
      select myNumber as x,
        num,
        pow,
        myNumber/num as div,
        myNumber%num as remain,
        (myNumber/num)::text as snafu
      from power5
      where myNumber/num > 0
      order by num desc
      limit 1)
    union all
    (
      select x,
        power5.num,
        power5.pow,
        processignpow5.remain/power5.num as div,
        processignpow5.remain%power5.num as remain,
        processignpow5.snafu || (processignpow5.remain/power5.num)::text
      from processignpow5
        inner join power5
          on processignpow5.pow-1 = power5.pow)
  )
  /* To make the snafu conversion easier, let's add a 0 in the front */
  select '0' || snafu || remain::text
  from processignpow5
  order by num
  limit 1
  ;
$pow5$ language sql;

create function snafu(mydec5 text) returns text as 
$snafu$
  with recursive snafu(n, pow5, snafu) as (
    select 1 as n,
      /* If we have a number over 2 as a last digit, then we need to increase
       * the "new" last digit.
       * By increasing that number we might reach 5, which is bad, obvisouly as
       * this is a number on base 5.
       * So, we then need to increate the next digit and put 0 instead as a last
       * digit.
       * That way, we should never get 5 in the snafu digit */
      case when pow5[array_length(pow5,1)]::int > 2 and array_length(pow5,1) > 3
        /* Now we can safely take into account what happens should we have
         * a 5 digit as a last number in our array */
        then
          case
            when pow5[array_length(pow5,1)-1] = '4'
              then por5[1:array_length(pow5,1)-3] || (pow5[array_length(pow5,1)-2]::int+1)::text || array['0']
            else pow5[1:array_length(pow5,1)-2] || (pow5[array_length(pow5,1)-1]::int+1)::text
          end
      /* We know we will never get 5 as the digit last digit shouldthe array
       * length be 1 because we on purpose added a 0 in front of the base
       * 5 number we had converted. */  
      when pow5[array_length(pow5,1)]::int > 2 and array_length(pow5,1) = 2
        then array[pow5[1]] || (pow5[2]::int+1)::text
      when pow5[array_length(pow5,1)]::int > 2 and array_length(pow5,1) = 3
        then
          case
            when pow5[array_length(pow5,1)-1] = '4'
              then array[pow5[1]] || (pow5[2]::int+1)::text || array['0']
            else array[pow5[1]] || (pow5[2]::int+1)::text || pow5[3]
          end
      else pow5[1:array_length(pow5,1)-1]
      end as pow5,
      case when pow5[array_length(pow5,1)]::int < 3 then pow5[array_length(pow5,1)]
        when pow5[array_length(pow5,1)]::int = 3 then '='
        when pow5[array_length(pow5,1)]::int = 4 then '-'
      end as snafu
    from (select string_to_array('013140400422344032342', null)) as finalpow5(pow5)
    union all
    select n+1,
      case when pow5[array_length(pow5,1)]::int > 2
        then por5[1:array_length(pow5,1)-2] || (pow5[array_length(pow5,1)-1]::int+1)::text
      else pow5[1:array_length(pow5,1)-1]
      end as pow5,
      (case when pow5[array_length(pow5,1)]::int < 3 then pow5[array_length(pow5,1)]
        when pow5[array_length(pow5,1)]::int = 3 then '='
        when pow5[array_length(pow5,1)]::int = 4 then '-'
        /* Due to the treatment from before round, we can end up with 5, so we
         * need to take that into account */
        when pow5[array_length(pow5,1)]::int = 5 then '0'
      end) || snafu.snafu as snafu
    from snafu
    where array_length(pow5,1) > 0
  )
  select
    case when substring(snafu from 1 for 1) = '0'
      then substring(snafu from 2)
    else snafu
    end as firstStar
  from snafu
  where snafu is not null
  order by n desc
  limit 1
  ;
$snafu$ language sql;

select snafu(pow5(sum(decNumber)::bigint)) as firstStar
from input;

Ce qui peut arriver

Les Verrous

• ACID
• Faire des transactions plus courtes
• N'utiliser le DML que quand c'est nécessaire
• Baisser le niveau d'isolation?
• Explorer le noSQL?

Dead Locks

• Plus de verrous → Plus grande probabilité
• Faire des transactions plus courtes
• N'utiliser le DML que quand c'est nécessaire
• Écrire les données dans le même ordre
• Baisser le niveau d'isolation?
• Explorer le noSQL?

Lag de replication

Lag de replication

Lag de replication

Lag de replication

• Les lois de la physique
• Un seul process pour appliquer les WALs
• Utilisez-vous la bonne archi?
• Scale up?

Ce qui est complexe

Xid wraparound

• Le nombre de numéros de transaction n'est pas infini
• Ça peut corrompre tout le cluster
• C'est dangereux!

Xid wraparound

• Quelle était l'alternative?
• ORA-01555 Snapshot Too Old
• ZHeap

Vacuum

• Garde l'ancienne version des lignes
• Jusqu'à ce que personne ne puisse les lire (dead rows)
• Vacuum "supprime" les dead rows

Vacuum analyze

• Vacuum
• et analyze!

Vacuum freeze

• Vacuum
• et freeze!

Vacuum full 🤷‍♀️

À retenir

• Prend des ressources
• Les valeurs par défauts sont mauvaises
• Difficile de deviner les bonnes valeurs
• Modifiez-les!

Collations

• Dépend de la glibc
• Lié à l'OS
• Peut corrompre les index et les données

Ce qui devrait être plus simple

Estimer le bloat?

-- new table bloat query
-- still needs work; is often off by +/- 20%
WITH constants AS (
    -- define some constants for sizes of things
    -- for reference down the query and easy maintenance
    SELECT current_setting('block_size')::numeric AS bs, 23 AS hdr, 8 AS ma
),
no_stats AS (
    -- screen out table who have attributes
    -- which dont have stats, such as JSON
    SELECT table_schema, table_name, 
        n_live_tup::numeric as est_rows,
        pg_table_size(relid)::numeric as table_size
    FROM information_schema.columns
        JOIN pg_stat_user_tables as psut
           ON table_schema = psut.schemaname
           AND table_name = psut.relname
        LEFT OUTER JOIN pg_stats
        ON table_schema = pg_stats.schemaname
            AND table_name = pg_stats.tablename
            AND column_name = attname 
    WHERE attname IS NULL
        AND table_schema NOT IN ('pg_catalog', 'information_schema')
    GROUP BY table_schema, table_name, relid, n_live_tup
),
null_headers AS (
    -- calculate null header sizes
    -- omitting tables which dont have complete stats
    -- and attributes which aren't visible
    SELECT
        hdr+1+(sum(case when null_frac <> 0 THEN 1 else 0 END)/8) as nullhdr,
        SUM((1-null_frac)*avg_width) as datawidth,
        MAX(null_frac) as maxfracsum,
        schemaname,
        tablename,
        hdr, ma, bs
    FROM pg_stats CROSS JOIN constants
        LEFT OUTER JOIN no_stats
            ON schemaname = no_stats.table_schema
            AND tablename = no_stats.table_name
    WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
        AND no_stats.table_name IS NULL
        AND EXISTS ( SELECT 1
            FROM information_schema.columns
                WHERE schemaname = columns.table_schema
                    AND tablename = columns.table_name )
    GROUP BY schemaname, tablename, hdr, ma, bs
),
data_headers AS (
    -- estimate header and row size
    SELECT
        ma, bs, hdr, schemaname, tablename,
        (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
        (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
    FROM null_headers
),
table_estimates AS (
    -- make estimates of how large the table should be
    -- based on row and page size
    SELECT schemaname, tablename, bs,
        reltuples::numeric as est_rows, relpages * bs as table_bytes,
    CEIL((reltuples*
            (datahdr + nullhdr2 + 4 + ma -
                (CASE WHEN datahdr%ma=0
                    THEN ma ELSE datahdr%ma END)
                )/(bs-20))) * bs AS expected_bytes,
        reltoastrelid
    FROM data_headers
        JOIN pg_class ON tablename = relname
        JOIN pg_namespace ON relnamespace = pg_namespace.oid
            AND schemaname = nspname
    WHERE pg_class.relkind = 'r'
),
estimates_with_toast AS (
    -- add in estimated TOAST table sizes
    -- estimate based on 4 toast tuples per page because we dont have 
    -- anything better.  also append the no_data tables
    SELECT schemaname, tablename, 
        TRUE as can_estimate,
        est_rows,
        table_bytes + ( coalesce(toast.relpages, 0) * bs ) as table_bytes,
        expected_bytes + ( ceil( coalesce(toast.reltuples, 0) / 4 ) * bs ) as expected_bytes
    FROM table_estimates LEFT OUTER JOIN pg_class as toast
        ON table_estimates.reltoastrelid = toast.oid
            AND toast.relkind = 't'
),
table_estimates_plus AS (
-- add some extra metadata to the table data
-- and calculations to be reused
-- including whether we cant estimate it
-- or whether we think it might be compressed
    SELECT current_database() as databasename,
            schemaname, tablename, can_estimate, 
            est_rows,
            CASE WHEN table_bytes > 0
                THEN table_bytes::NUMERIC
                ELSE NULL::NUMERIC END
                AS table_bytes,
            CASE WHEN expected_bytes > 0 
                THEN expected_bytes::NUMERIC
                ELSE NULL::NUMERIC END
                    AS expected_bytes,
            CASE WHEN expected_bytes > 0 AND table_bytes > 0
                AND expected_bytes <= table_bytes
                THEN (table_bytes - expected_bytes)::NUMERIC
                ELSE 0::NUMERIC END AS bloat_bytes
    FROM estimates_with_toast
    UNION ALL
    SELECT current_database() as databasename, 
        table_schema, table_name, FALSE, 
        est_rows, table_size,
        NULL::NUMERIC, NULL::NUMERIC
    FROM no_stats
),
bloat_data AS (
    -- do final math calculations and formatting
    select current_database() as databasename,
        schemaname, tablename, can_estimate, 
        table_bytes, round(table_bytes/(1024^2)::NUMERIC,3) as table_mb,
        expected_bytes, round(expected_bytes/(1024^2)::NUMERIC,3) as expected_mb,
        round(bloat_bytes*100/table_bytes) as pct_bloat,
        round(bloat_bytes/(1024::NUMERIC^2),2) as mb_bloat,
        table_bytes, expected_bytes, est_rows
    FROM table_estimates_plus
)
-- filter output for bloated tables
SELECT databasename, schemaname, tablename,
    can_estimate,
    est_rows,
    pct_bloat, mb_bloat,
    table_mb
FROM bloat_data
-- this where clause defines which tables actually appear
-- in the bloat chart
-- example below filters for tables which are either 50%
-- bloated and more than 20mb in size, or more than 25%
-- bloated and more than 4GB in size
WHERE ( pct_bloat >= 50 AND mb_bloat >= 10 )
    OR ( pct_bloat >= 25 AND mb_bloat >= 1000 )
ORDER BY mb_bloat DESC;

Estimer le bloat?

-- btree index stats query
-- estimates bloat for btree indexes
WITH btree_index_atts AS (
    SELECT nspname, 
        indexclass.relname as index_name, 
        indexclass.reltuples, 
        indexclass.relpages, 
        indrelid, indexrelid,
        indexclass.relam,
        tableclass.relname as tablename,
        regexp_split_to_table(indkey::text, ' ')::smallint AS attnum,
        indexrelid as index_oid
    FROM pg_index
    JOIN pg_class AS indexclass ON pg_index.indexrelid = indexclass.oid
    JOIN pg_class AS tableclass ON pg_index.indrelid = tableclass.oid
    JOIN pg_namespace ON pg_namespace.oid = indexclass.relnamespace
    JOIN pg_am ON indexclass.relam = pg_am.oid
    WHERE pg_am.amname = 'btree' and indexclass.relpages > 0
         AND nspname NOT IN ('pg_catalog','information_schema')
    ),
index_item_sizes AS (
    SELECT
    ind_atts.nspname, ind_atts.index_name, 
    ind_atts.reltuples, ind_atts.relpages, ind_atts.relam,
    indrelid AS table_oid, index_oid,
    current_setting('block_size')::numeric AS bs,
    8 AS maxalign,
    24 AS pagehdr,
    CASE WHEN max(coalesce(pg_stats.null_frac,0)) = 0
        THEN 2
        ELSE 6
    END AS index_tuple_hdr,
    sum( (1-coalesce(pg_stats.null_frac, 0)) * coalesce(pg_stats.avg_width, 1024) ) AS nulldatawidth
    FROM pg_attribute
    JOIN btree_index_atts AS ind_atts ON pg_attribute.attrelid = ind_atts.indexrelid AND pg_attribute.attnum = ind_atts.attnum
    JOIN pg_stats ON pg_stats.schemaname = ind_atts.nspname
          -- stats for regular index columns
          AND ( (pg_stats.tablename = ind_atts.tablename AND pg_stats.attname = pg_catalog.pg_get_indexdef(pg_attribute.attrelid, pg_attribute.attnum, TRUE)) 
          -- stats for functional indexes
          OR   (pg_stats.tablename = ind_atts.index_name AND pg_stats.attname = pg_attribute.attname))
    WHERE pg_attribute.attnum > 0
    GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9
),
index_aligned_est AS (
    SELECT maxalign, bs, nspname, index_name, reltuples,
        relpages, relam, table_oid, index_oid,
        coalesce (
            ceil (
                reltuples * ( 6 
                    + maxalign 
                    - CASE
                        WHEN index_tuple_hdr%maxalign = 0 THEN maxalign
                        ELSE index_tuple_hdr%maxalign
                      END
                    + nulldatawidth 
                    + maxalign 
                    - CASE /* Add padding to the data to align on MAXALIGN */
                        WHEN nulldatawidth::integer%maxalign = 0 THEN maxalign
                        ELSE nulldatawidth::integer%maxalign
                      END
                )::numeric 
              / ( bs - pagehdr::NUMERIC )
              +1 )
         , 0 )
      as expected
    FROM index_item_sizes
),
raw_bloat AS (
    SELECT current_database() as dbname, nspname, pg_class.relname AS table_name, index_name,
        bs*(index_aligned_est.relpages)::bigint AS totalbytes, expected,
        CASE
            WHEN index_aligned_est.relpages <= expected 
                THEN 0
                ELSE bs*(index_aligned_est.relpages-expected)::bigint 
            END AS wastedbytes,
        CASE
            WHEN index_aligned_est.relpages <= expected
                THEN 0 
                ELSE bs*(index_aligned_est.relpages-expected)::bigint * 100 / (bs*(index_aligned_est.relpages)::bigint) 
            END AS realbloat,
        pg_relation_size(index_aligned_est.table_oid) as table_bytes,
        stat.idx_scan as index_scans
    FROM index_aligned_est
    JOIN pg_class ON pg_class.oid=index_aligned_est.table_oid
    JOIN pg_stat_user_indexes AS stat ON index_aligned_est.index_oid = stat.indexrelid
),
format_bloat AS (
SELECT dbname as database_name, nspname as schema_name, table_name, index_name,
        round(realbloat) as bloat_pct, round(wastedbytes/(1024^2)::NUMERIC) as bloat_mb,
        round(totalbytes/(1024^2)::NUMERIC,3) as index_mb,
        round(table_bytes/(1024^2)::NUMERIC,3) as table_mb,
        index_scans
FROM raw_bloat
)
-- final query outputting the bloated indexes
-- change the where and order by to change
-- what shows up as bloated
SELECT *
FROM format_bloat
WHERE ( bloat_pct > 50 and bloat_mb > 10 )
ORDER BY bloat_pct DESC;

Explorer les droits d'un user?

WITH server_permissions AS (
        SELECT 
            r.rolname, 
            'Server_Permissions' AS "Level", 
            r.rolsuper, 
            r.rolinherit,
            r.rolcreaterole, 
            r.rolcreatedb, 
            r.rolcanlogin,
            ARRAY(
                SELECT b.rolname
                FROM pg_catalog.pg_auth_members m
                JOIN pg_catalog.pg_roles b ON m.roleid = b.oid
                WHERE m.member = r.oid
            ) AS memberof,
            r.rolbypassrls
        FROM pg_catalog.pg_roles r
        WHERE r.rolname !~ '^pg_'
    ),
    
    db_ownership AS (
        SELECT 
            r.rolname, 
            'DB_Ownership' AS "Level", 
            d.datname
        FROM pg_catalog.pg_database d, pg_catalog.pg_roles r
        WHERE d.datdba = r.oid
    ),
    
    schema_permissions AS (
        SELECT
            'Schema Permissions' AS "Level",                
            r.rolname AS role_name,
            nspname AS schema_name,
            pg_catalog.has_schema_privilege(r.rolname, nspname, 'CREATE') AS create_grant,
            pg_catalog.has_schema_privilege(r.rolname, nspname, 'USAGE') AS usage_grant
        FROM pg_namespace pn, pg_catalog.pg_roles r
        WHERE array_to_string(nspacl, ',') LIKE '%' || r.rolname || '%' 
              AND nspowner > 1
    ),
    
    table_ownership AS (
        SELECT 
            'Table Ownership' AS "Level",
            tableowner, 
            schemaname, 
            tablename
        FROM pg_tables
        GROUP BY tableowner, schemaname, tablename
    ),
    
    object_permissions AS (
        SELECT  
            'Object Permissions' AS "Level",
            COALESCE(NULLIF(s[1], ''), 'public') AS rolname,
            n.nspname,
            relname, 
            CASE 
                WHEN relkind = 'm' THEN 'Materialized View'
                WHEN relkind = 'p' THEN 'Partitioned Table'
                WHEN relkind = 'S' THEN 'Sequence'
                WHEN relkind = 'I' THEN 'Partitioned Index'
                WHEN relkind = 'v' THEN 'View'
                WHEN relkind = 'i' THEN 'Index'
                WHEN relkind = 'c' THEN 'Composite Type'
                WHEN relkind = 't' THEN 'TOAST table'
                WHEN relkind = 'r' THEN 'Table'
                WHEN relkind = 'f' THEN 'Foreign Table'
            END AS "Object Type",
            s[2] AS privileges
        FROM 
            pg_class c
            JOIN pg_namespace n ON n.oid = relnamespace
            JOIN pg_roles r ON r.oid = relowner,
            UNNEST(COALESCE(relacl::text[], FORMAT('{%s=arwdDxt/%s}', rolname, rolname)::text[])) acl, 
            REGEXP_SPLIT_TO_ARRAY(acl, '=|/') s 
        WHERE relkind <> 'i' AND relkind <> 't'
    )   
    
    SELECT 
        "Level", 
        rolname AS "Role", 
        'N/A' AS "Object Name", 
        'N/A' AS "Schema Name", 
        'N/A' AS "DB Name", 
        'N/A' AS "Object Type", 
        'N/A' AS "Privileges", 
        rolsuper::text AS "Is SuperUser", 
        rolinherit::text,
        rolcreaterole::text, 
        rolcreatedb::text, 
        rolcanlogin::text,
        memberof::text,
        rolbypassrls::text 
    FROM server_permissions
    
    UNION
    
    SELECT 
        dow."Level", 
        dow.rolname,
        'N/A',  
        'N/A', 
        datname,
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A'
    FROM db_ownership AS dow 
    
    UNION
    
    SELECT
        "Level", 
        role_name, 
        'N/A', 
        schema_name, 
        'N/A', 
        'N/A',
        CASE 
            WHEN create_grant IS TRUE AND usage_grant IS TRUE THEN 'Usage+Create' 
            WHEN create_grant IS TRUE AND usage_grant IS FALSE THEN 'Create' 
            WHEN create_grant IS FALSE AND usage_grant IS TRUE THEN 'Usage' 
            ELSE 'None' 
        END, 
        'N/A', 
        'N/A', 
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A'
    FROM schema_permissions
    
    UNION
    
    SELECT 
        "Level", 
        tableowner, 
        tablename, 
        schemaname,
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A'
    FROM table_ownership
    
    UNION
    
    SELECT 
        "Level", 
        rolname, 
        relname,  
        nspname, 
        'N/A', 
        "Object Type", 
        privileges,
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A'
    FROM object_permissions
    ORDER BY "Role";

Updates de versions majeures

• Flexibilité
• Support de 5 versions/années majeures
• Faites-le!

Partitionnement

• Pas de création automatique de partition
• Surtout pour les time series

Pour résumer

• PostgreSQL est bon
• Souvent, le problème, c'est vous
• Il y a quelques pain points
• Ils ne sont pas faciles à résoudre
• Vous pouvez aider!!!

Des questions?